package com.wondertek.poms.dao.repo;

import com.wondertek.poms.dao.po.PrdCont;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;

/**
 * @author chenjijiang
 * @description
 */
@Repository
public interface IPrdContDao extends JpaRepository<PrdCont, Long> {

	PrdCont findByPrdContId(Long prdContId);


	/**
	 * 更新节目新发布状态
	 *
	 * @param publishNoVOMSStatus 节目新发布状态
	 * @return 更新成功
	 * @author gaozhengxing gaozhenghang@wondertek.com.cn
	 * @date 2020/12/30 13:55
	 */
	@Transactional
	@Modifying
	@Query("update PrdCont sc set sc.publishNoVOMSStatus = ?1 where sc.prdContId = ?2 ")
	int updatePublishNoVOMSStatus(int publishNoVOMSStatus, Long prdContId);

	/**
	 * 根据节目id查询内容新发布状态
	 *
	 * @param prdContId 节目
	 * @return 节目
	 * @author gaozhengxing gaozhenghang@wondertek.com.cn
	 * @date 2020/12/30 13:55
	 */
	@Query(value = "SELECT pc.PUBLISH_NO_VOMS_STATUS FROM prd_cont pc WHERE pc.prd_cont_id =?1",
			nativeQuery = true)
	Integer findNewPubStatusByPrdContId(Long prdContId);

	/**
	 * 根据内容id查询所有节目
	 *
	 * @param contentId 内容id
	 * @param isDelete  是否删除 默认0 未删除
	 * @return 节目
	 * @author gaozhengxing gaozhenghang@wondertek.com.cn
	 * @date 2020/12/30 13:55
	 */
	List<PrdCont> findAllByContentIdAndIsDelete(String contentId, int isDelete);

	/**
	 * <p><b>Title:</b> getPrdContByMmsId</p>
	 * <p><b>Description:</b> 根据媒资id查询所有节目</p>
	 *
	 * @param assetId
	 * @return
	 * @author douzi
	 */
	@Query(value = "SELECT" +
			" pc.*" +
			" FROM" +
			" prd_cont pc," +
			" PRD_INFO pi" +
			" WHERE" +
			" pc.prd_info_id = pi.prdinfoid" +
			" AND pc.is_delete = 0 " +
			" AND pc.file_action <> 2" +
			" AND pc.mms_id =?1",
			nativeQuery = true)
	List<PrdCont> getPrdContByAssetId(Long assetId);

	/**
	 * <p><b>Title:</b> getPrdContByMmsId</p>
	 * <p><b>Description:</b> 根据媒资id查询二级产品下的节目</p>
	 *
	 * @param assetId
	 * @param productInfoPackageId
	 * @return
	 * @author douzi
	 */
	@Query(value = "SELECT" +
			" pc.*" +
			" FROM" +
			" prd_cont pc," +
			" PRD_INFO pi" +
			" WHERE" +
			" pc.prd_info_id = pi.prdinfoid" +
			" AND pc.is_delete = 0 " +
			" AND pc.file_action <> 2" +
			" AND pc.mms_id =?1" +
			" AND pi.PRODUCT_INFO_PACKAGE_ID =?2",
			nativeQuery = true)
	List<PrdCont> getPrdContByMmsId(Long assetId, Long productInfoPackageId);

	/**
	 * <p><b>Title:</b> getPrdContByMmsId</p>
	 * <p><b>Description:</b> 根据媒资id查询商品包下的节目</p>
	 *
	 * @param assetId
	 * @param prdInfoId
	 * @return
	 * @author douzi
	 */
	@Query(value = "SELECT" +
			" pc.*" +
			" FROM" +
			" prd_cont pc" +
			" WHERE" +
			" pc.is_delete = 0 " +
			" AND pc.file_action <> 2" +
			" AND pc.mms_id =?1" +
			" AND pc.PRD_INFO_ID =?2",
			nativeQuery = true)
	List<PrdCont> getPrdContByMmsIdInPrdInfo(Long assetId, Long prdInfoId);

	/**
	 * <p><b>Title:</b> getSerialCountOfPublish</p>
	 * <p><b>Description:</b> 获取所有正片列表</p>
	 *
	 * @param prdInfoId
	 * @param mssId
	 * @return
	 * @author douzi
	 */
	@Query(value = "SELECT pc.* FROM prd_cont pc, PC_MEDIA_RELATION mr "
			+ "WHERE pc.mms_id = mr.ASSET_ID AND pc.is_delete = 0 AND pc.file_action != 2 "
			+ "AND mr.serial_sequence is not null "
			+ "AND mr.PARENT_ASSET_ID = ?2 AND pc.prd_info_id = ?1", nativeQuery = true)
	List<PrdCont> getSerialsAllPublish(Long prdInfoId, Long mssId);

	/**
	 * <p><b>Title:</b> findPcByContentId</p>
	 * <p><b>Description:</b> 根据内容id获取二级产品下的所有节目</p>
	 * @author douzi
	 * @param liveContentId
	 * @param productInfoPackageId
	 * @return
	 */
	@Query(value = "SELECT" +
			" pc.*" +
			" FROM" +
			" prd_cont pc," +
			" PRD_INFO pi" +
			" WHERE" +
			" pc.prd_info_id = pi.prdinfoid" +
			" AND pc.is_delete = 0 " +
			" AND pc.file_action <> 2" +
			" AND pc.content_id =?1" +
			" AND pi.PRODUCT_INFO_PACKAGE_ID =?2",
			nativeQuery = true)
	List<PrdCont> findPcByContentId(String contentId, Long productInfoPackageId);

	/**
	 * <p><b>Title:</b> findPcByContentIdInPrdInfo</p>
	 * <p><b>Description:</b> 根据内容id,商品包id获取节目</p>
	 * @author douzi
	 * @param contentId 内容id
	 * @param prdInfoId 商品包id
	 * @return
	 */
	@Query(value = "SELECT" +
			" pc.*" +
			" FROM" +
			" prd_cont pc" +
			" WHERE" +
			" pc.is_delete = 0 " +
			" AND pc.file_action <> 2" +
			" AND pc.content_id =?1" +
			" AND pc.PRD_INFO_ID =?2  order by pc.PRD_CONT_ID",
			nativeQuery = true)
	List<PrdCont> findPcByContentIdInPrdInfo(String contentId, Long prdInfoId);

	/**
	 * <p><b>Title:</b> findPcByContentIdInPrdInfo</p>
	 * <p><b>Description:</b> 根据内容id和二级产品id获取节目</p>
	 * @author douzi
	 * @param contentId 内容id
	 * @param packageId2 二级产品id
	 * @return
	 */
	@Query(value = "SELECT" +
			" pc.*" +
			" FROM" +
			" prd_cont pc" +
			" WHERE" +
			" pc.is_delete = 0 " +
			" AND pc.file_action <> 2" +
			" AND pc.content_id =?1" +
			" AND pc.PRD_INFO_ID in " +
			"( SELECT pip.PRDINFOID from PRD_INFO pip where pip.PRODUCT_INFO_PACKAGE_ID =?2)" +
			"  order by pc.PRD_CONT_ID",
			nativeQuery = true)
	List<PrdCont> findPcByContentIdInPackageId2(String contentId, Long packageId2);

	/**
	 * <p><b>Title:</b> findPcByContentIdInPrdInfo</p>
	 * <p><b>Description:</b> 根据内容id获取商品包下的所有节目</p>
	 * @author gzx
	 * @param contentId
	 * @param prdInfoId
	 * @return
	 */
	@Query(value = "SELECT" +
			" pc.*" +
			" FROM" +
			" prd_cont pc" +
			" WHERE  pc.content_id =?1" +
			" AND pc.PRD_INFO_ID =?2  order by pc.PRD_CONT_ID",
			nativeQuery = true)
	List<PrdCont> findAllPcByContentIdInPrdInfo(String contentId, Long prdInfoId);

	/**
	 * <p><b>Title:</b> findPcByContentIdInPrdInfo</p>
	 * <p><b>Description:</b> 根据内容id获取商品包下的所有节目</p>
	 * @author gzx
	 * @param contentId
	 * @param prdInfoId
	 * @return
	 */
	@Query(value = "SELECT" +
			" pc.*" +
			" FROM" +
			" prd_cont pc" +
			" WHERE  pc.content_id =?1" +
			" AND pc.PRD_INFO_ID =in " +
			"( SELECT pip.PRDINFOID from PRD_INFO pip where pip.PRODUCT_INFO_PACKAGE_ID =?2)" +
			"order by pc.PRD_CONT_ID",
			nativeQuery = true)
	List<PrdCont> findAllPcByContentIdInPackageId2(String contentId, Long prdInfoId);

	/**
	 * <p><b>Title:</b> getTotalUseOfEquity</p>
	 * <p><b>Description:</b> 查看带有指定权益节点的节目数量</p>
	 * @author bjw
	 * @param id
	 * @return
	 */
	@Query(value = "select count(*) from PRD_CONT where is_delete = 0 AND file_action <> 2 and EQUITY_ID like ?1 ",
			nativeQuery = true)
	int getTotalUseOfEquity(String id);

	/**
	 * 查询节目数
	 *
	 * @param contentId 内容id
	 * @param prdInfoId 节目id
	 * @return 节目数
	 * @author gaozhengxing gaozhenghang@wondertek.com.cn
	 * @date 2020/12/30 11:23
	 */
	@Query(value = "select count(*) from PRD_CONT where IS_DELETE = 0 AND file_action <> 2 and CONTENT_ID = ?1 and PRD_INFO_ID = ?2",
			nativeQuery = true)
	int getCountByContIdAndPrdInfoId(String contentId, Long prdInfoId);

	/**
	 * 更新紧急状况
	 *
	 * @param isEmergency 0-非紧急，1-紧急
	 * @param prdContIds  节目id
	 */

	@Modifying
	@Query(value = "update PRD_CONT set IS_EMERGENCY = ?1 where PRD_CONT_ID in (?2)", nativeQuery = true)
	int batchUpdateIsEmergencyById(String isEmergency, List<Long> prdContIds);

	/**
	 * @param prdContId 节目id
	 * @param prdInfoId 商品包id
	 * @return 更新节目的商品包id
	 */
	@Transactional
	@Modifying
	@Query(value = "update PRD_CONT set PRD_INFO_ID =?1 where PRD_CONT_ID=?2", nativeQuery = true)
	int updatePrdInfoId(Long prdInfoId, Long prdContId);


	@Query(value = "select * from PRD_CONT where  CONTENT_ID in (select CONTENT_ID from CONTENT where SERIAL_CONTENT_ID = ?1) and " +
			"PRD_INFO_ID=?2 and FILE_ACTION!=2 and IS_DELETE=0", nativeQuery = true)
	List<PrdCont> findAllSubSetPrdConts(String serialContentId, Long prdInfoId);

	/***
	 * 根据媒资id和商品包ID去查询媒资壳
	 * @param mssId
	 * @param prdInfoId
	 * @return
	 */
	@Query(value = "SELECT pc.* FROM prd_cont pc, PC_MEDIA_RELATION mr "
			+ "WHERE pc.mms_id = mr.ASSET_ID "
			+ "AND mr.ASSET_ID = (select mmr.PARENT_ASSET_ID from PC_MEDIA_RELATION mmr where mmr.ASSET_ID = ?1) AND pc.prd_info_id = ?2", nativeQuery = true)
	List<PrdCont> getShellPrdContByMsssIdAndPrdInfoId(Long mssId, Long prdInfoId);
	
	@Query(value = "select pc.* from prd_cont pc WHERE pc.content_id =?1 order by prd_cont_id", nativeQuery = true)
	List<PrdCont> getAllList();
}
